package cn.edu.sanxiau.www.dao.impl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.sanxiau.www.dao.UserDao;
import cn.edu.sanxiau.www.model.User;
import frame.utils.dbutil.JdbcUtils;

public class UserDaoImpl implements UserDao {

	@Override
	public User queryUserByUser(User user) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM sys_user WHERE userName=? and password=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getUserName());
			pstmt.setString(2, user.getPassword());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				User user_db = new User();
				user_db.setUserId(rs.getInt("userId"));
				user_db.setName(rs.getString("name"));
				user_db.setSex(rs.getString("sex"));
				user_db.setAdress(rs.getString("adress"));
				user_db.setTel(rs.getString("tel"));
				user_db.setUserName(rs.getString("userName"));
				user_db.setPassword(rs.getString("password"));
				return user_db;
			}
		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<User> queryAllUser() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM sys_user";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			List<User> user_db_list = new ArrayList<User>();

			while (rs.next()) {
				User user_db = new User();
				user_db.setUserId(rs.getInt("userId"));
				user_db.setName(rs.getString("name"));
				user_db.setSex(rs.getString("sex"));
				user_db.setAdress(rs.getString("adress"));
				user_db.setTel(rs.getString("tel"));
				user_db.setUserName(rs.getString("userName"));
				user_db.setPassword(rs.getString("password"));

				user_db_list.add(user_db);
			}

			return user_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int addUserByUser(User user) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "INSERT INTO  sys_user (userId,name,sex,adress,tel,userName,password) VALUES(?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, user.getUserId());
			pstmt.setString(2, user.getName());
			pstmt.setString(3, user.getSex());
			pstmt.setString(4, user.getAdress());
			pstmt.setString(5, user.getTel());
			pstmt.setString(6, user.getUserName());
			pstmt.setString(7, user.getPassword());

			int m = pstmt.executeUpdate();
			return m;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int deleteUserByUserId(int userId) {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = JdbcUtils.getConnection();

			String sql = "DELETE FROM sys_user WHERE userId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, userId);

			int m = pstmt.executeUpdate();
			return m;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public User queryUserByUserId(int userId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM sys_user WHERE userId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, userId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				User user_db = new User();
				user_db.setUserId(rs.getInt("userId"));
				user_db.setName(rs.getString("name"));
				user_db.setSex(rs.getString("sex"));
				user_db.setAdress(rs.getString("adress"));
				user_db.setTel(rs.getString("tel"));
				user_db.setUserName(rs.getString("userName"));
				user_db.setPassword(rs.getString("password"));
				return user_db;
			}
		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int updateUserByUser(User user) {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = JdbcUtils.getConnection();
			String sql = "UPDATE sys_user SET name=?,sex=?,adress=?,tel=?,userName=? ,password=? WHERE userId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getName());
			pstmt.setString(2, user.getSex());
			pstmt.setString(3, user.getAdress());
			pstmt.setString(4, user.getTel());
			pstmt.setString(5, user.getUserName());
			pstmt.setString(6, user.getPassword());
			pstmt.setInt(7, user.getUserId());

			int m = pstmt.executeUpdate();
			return m;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}



}
